package com.jh.wdbc.controller;

import java.util.HashSet;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import java.util.StringTokenizer;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.jh.wdbc.model.ConnectionInfo;
import com.jh.wdbc.model.ResultModel;
import com.jh.wdbc.service.ConnectionManager;
import com.jh.wdbc.service.DataBaseConnectionService;
import com.jh.wdbc.service.ExecutionService;

/**
 * Handles requests for the application home page.
 */
@Controller
public class HomeController {
	/**
	 * Simply selects the home view to render by returning its name.
	 */
	@Autowired
	private ExecutionService executionService;

	@Autowired
	private DataBaseConnectionService connectionService;

	@Autowired
	@Qualifier(value = "oraProp")
	Properties prop;

	@RequestMapping(value = "/home")
	public String index() {
		return "home";
	}

	@RequestMapping(value = "/editor")
	public String editor() {
		return "editor";
	}

	@RequestMapping(value = "/execute")
	public String execute(String query, String dbid, Model model) throws Exception {
		query = query.trim().replace(";", "");
		StringTokenizer st = new StringTokenizer(query, " ");
		String head = st.nextToken();
		head = head.toUpperCase();
		head = head.trim();
		System.out.println(head);
		model.addAttribute("dbid", dbid);
		model.addAttribute("isQuery", "Y");
		try {
			if (head.startsWith("SELECT") || head.startsWith("DESC") || head.startsWith("SHOW")) {
				ResultModel result = executionService.executeQuery(query, dbid);
				model.addAttribute("rm", result);
				if (result.getTableCount() == 1) {
					String table = (String)result.getTableList().toArray()[0];
					List<String> pk = executionService.getUniqueColumns(dbid, table);
					if (pk.size() == 0) {
						model.addAttribute("editable", false);
					} else {
						model.addAttribute("pk", pk);
						model.addAttribute("table", table);
					}
				} else {
					model.addAttribute("editable", false);
				}
			} else {
				int result = executionService.executeUpdate(query, dbid);
				model.addAttribute("count", result);
			}
		} catch (Exception e) {
			ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbid);
			String msg = e.getMessage();
			if (connectionInfo.getDbType().contains("Oracle")) {
				msg = msg.substring(0, msg.indexOf(":"));
				model.addAttribute("code", msg);
				String desc = prop.getProperty(msg.toLowerCase());
				msg = msg + ":" + desc;
			}

			model.addAttribute("error", msg);
		}

		return "contents";
	}

	@RequestMapping("tablelist")
	public String connectDB(String phase, String dbid, Model model, HttpServletRequest request, HttpServletResponse response) {
		Set<String> connectionList = connectionService.getConnectionInfoHistory(request);

		if (StringUtils.isEmpty(dbid)) {
			return "tablelist";
		}

		String dbName = phase + "." + dbid;
		if (StringUtils.isEmpty(phase)) {
			dbName = dbid;
		}
		try {
			if (StringUtils.isNotEmpty(dbName)) {
				model.addAttribute("rm", executionService.getTableList(dbName));
				model.addAttribute("dbid", dbName);
			}
		} catch (Exception e) {
			model.addAttribute("dbid", "error");
			return "tablelist";
		}

		if (connectionList == null) {
			connectionList = new HashSet<String>();
		}
		connectionList.add(dbName);
		return "tablelist";
	}

	@RequestMapping("tabledesc")
	public String tableDesc(String dbid, String table, Model model) throws Exception {
		if (StringUtils.isNotEmpty(dbid) || StringUtils.isNotEmpty(table)) {
			model.addAttribute("desc", executionService.getTableDesc(dbid, table));
			model.addAttribute("index", executionService.getIndexList(dbid, table));
			model.addAttribute("table", table);
		}
		return "desc";
	}

	@RequestMapping("plan")
	public String plan(String dbid, String query, Model model) throws Exception {
		query = query.trim().replace(";", "");
		try {
			if (StringUtils.isNotEmpty(dbid) || StringUtils.isNotEmpty(query)) {
				model.addAttribute("rm", executionService.getPlan(dbid, query));
				model.addAttribute("plan", "Y");
			}
		} catch (Exception e) {
			ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbid);
			String msg = e.getMessage();
			if (connectionInfo.getDbType().contains("Oracle")) {
				msg = msg.substring(0, msg.indexOf(":"));
				model.addAttribute("code", msg);
				String desc = prop.getProperty(msg.toLowerCase());
				msg = msg + ":" + desc;
				model.addAttribute("error", msg);
			}
		}
		return "contents";
	}

	@RequestMapping("dbpm")
	public String dbpm(String phase, String dbid, Model model) throws Exception {
		String dbids[] = dbid.split("\\.");
		StringBuilder sb = new StringBuilder(dbids[1]);
		for (int i = 2; i < dbids.length; i++) {
			sb.append(".").append(dbids[i]);
		}
		dbid = sb.toString();

		ConnectionInfo connectionInfo[] = new ConnectionInfo[3];
		connectionInfo[0] = ConnectionManager.getConnectionInfoFromDBPM("dev", dbid);
		connectionInfo[1] = ConnectionManager.getConnectionInfoFromDBPM("alpha", dbid);
		connectionInfo[2] = ConnectionManager.getConnectionInfoFromDBPM("real", dbid);
		model.addAttribute("connectionInfo", connectionInfo);
		model.addAttribute("phase", dbids[0]);
		return "dbpm";
	}

	@RequestMapping("contents")
	public String contents(String dbid, String table, String start, Model model) throws Exception {
		if (StringUtils.isEmpty(start)) {
			start = "0";
		}
		ResultModel rm = executionService.getContents(dbid, table, Integer.parseInt(start));
		List<String> pk = executionService.getUniqueColumns(dbid, table);
		model.addAttribute("dbid", dbid);
		model.addAttribute("table", table);
		model.addAttribute("start", start);
		model.addAttribute("rm", rm);
		model.addAttribute("end", Integer.parseInt(start) + rm.getResults().size());
		model.addAttribute("total", executionService.getTotalCount(dbid, table));
		if (pk.size() == 0) {
			model.addAttribute("editable", false);
		} else {
			model.addAttribute("pk", pk);
		}
		return "contents";
	}

	@RequestMapping("modify")
	public String modify(String dbid, String table, String columns, String values, String mcol, String mval, String start, Model model, String isQuery) throws Exception {
		executionService.modifyContent(dbid, table, columns, values, mcol, mval);
		if ("Y".equals(isQuery)) {
			return "result";
		}
		return contents(dbid, table, start, model);
	}
}
